﻿using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL
{
    public partial class C_OilCardRefuelDAL : BaseDAL<C_OilCardRefuel>
    {
        public static readonly SqlConnectionStringBuilder ManageBuilder = new SqlConnectionStringBuilder(SqlHelper.MainConnectionString);//取管理库的链接字符串
        public static readonly string ManageDatabase = ManageBuilder.InitialCatalog;
        #region
        //查询油卡加油记录列表
        //保留字段bakup1=卡内金额，bakup2=卡内余额
        public object GetRefuelList(int page, int rows, string start, string end, string center, string station, string cardCode, string actualCode, string ambulanceNumber, 
                                     string oilType, string shift ,E_StatisticsPermisson em, string selfWorkCode,string selfName, string selfCenterID, string selfStationID)
        {          
            List<RefuelExt> list = new List<RefuelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct identity(int,1,1) as 行号,编码=ocr.ID,卡号=ocr.CardCode,司机工号=ocr.DriverWorkID,司机姓名=ocr.DriverName,加油金额=Convert(decimal(18,2),ocr.RefuelMoney),
                        备注=ocr.Remark,车牌号码=ocr.ActualCode,填写人=ocr.FillPerson,填写时间=ocr.FillTime,分中心编码=ocr.CenterID,
                        加油时间=ocr.RefuelTime,分中心=tc.名称,分站编码=ocr.StationID,分站=ts.名称,车辆编号=ocr.AmbulanceNumber,班次=ocr.Shift,
                        加油油号=ocr.OilType,加油量=Convert(decimal(18,2),ocr.OilMass),上次加油路码=Convert(decimal(18,2),ocr.LastGasPathCode),本次加油路码=Convert(decimal(18,2),ocr.GasPathCode)
                        ,行驶公里=Convert(decimal(18,2),ocr.DrivingKM),车辆编码=ocr.AmbulanceCode,加油人=ocr.RefuelPerson,凭证编号=ocr.ProofNumber,卡内金额=ocr.bakup1,卡内余额=ocr.bakup2
                        into #tempa                   
                        from C_OilCardRefuel ocr 
                        left join C_Ambulance ca on ca.AmbulanceCode = ocr.AmbulanceCode
                       ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on ocr.StationID=ts.编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on ocr.CenterID=tc.编码 ");
            sb.Append(" where 1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("ocr.RefuelTime", Convert.ToDateTime(start), sb);
            WhereClauseUtility.AddDateTimeLessThan("ocr.RefuelTime", Convert.ToDateTime(end), sb);          
            WhereClauseUtility.AddStringEqual("ocr.CardCode", cardCode, sb);      
            WhereClauseUtility.AddStringEqual("ocr.OilType", oilType, sb);
            WhereClauseUtility.AddStringEqual("ocr.Shift", shift, sb);
            if (em == E_StatisticsPermisson.None)
                return null;

            else
            {
                if (em == E_StatisticsPermisson.ALL)
                {
                    WhereClauseUtility.AddStringEqual("ca.CenterCode", center, sb);
                    WhereClauseUtility.AddStringEqual("ca.StationCode", station, sb);
                    WhereClauseUtility.AddStringEqual("ocr.ActualCode", actualCode, sb);
                    WhereClauseUtility.AddStringEqual("ocr.AmbulanceNumber", ambulanceNumber, sb);
                }
                else if (em == E_StatisticsPermisson.CENTER)
                {
                    WhereClauseUtility.AddStringEqual("ca.CenterCode", selfCenterID, sb);
                    WhereClauseUtility.AddStringEqual("ca.StationCode", station, sb);
                    WhereClauseUtility.AddStringEqual("ocr.ActualCode", actualCode, sb);
                    WhereClauseUtility.AddStringEqual("ocr.AmbulanceNumber", ambulanceNumber, sb);
                }
                else if (em == E_StatisticsPermisson.STATION)
                {
                    sb.Append("and 1=2 ");
                    WhereClauseUtility.AddStringEqual("ca.CenterCode", center, sb);
                    WhereClauseUtility.AddStringEqual("ca.StationCode", selfStationID, sb);
                    WhereClauseUtility.AddStringEqual("ocr.ActualCode", actualCode, sb);
                    WhereClauseUtility.AddStringEqual("ocr.AmbulanceNumber", ambulanceNumber, sb);
                }
                else if (em == E_StatisticsPermisson.SELF)
                {
                    WhereClauseUtility.AddStringEqual("ca.CenterCode", center, sb);
                    WhereClauseUtility.AddStringEqual("ca.StationCode", station, sb);
                    WhereClauseUtility.AddStringEqual("ocr.RefuelPerson", selfName, sb);                
                    WhereClauseUtility.AddStringEqual("ocr.ActualCode", actualCode, sb);
                    WhereClauseUtility.AddStringEqual("ocr.AmbulanceNumber", ambulanceNumber, sb);
                }
                sb.Append(" order by 加油时间 desc ");
                sb.Append(@" select * into #temp from #tempa where 1=1 ");
                sb.Append(@" select top " + rows + " A.*  from #temp A where 行号>" + (page - 1) * rows + " order by 行号 ");
                sb.Append(@"  SELECT count(*) FROM #temp t   drop table #tempa,#temp ");

                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        RefuelExt info = new RefuelExt();
                        info.CenterID = dr["分中心编码"].ToString();
                        info.StationID = dr["分站编码"].ToString();
                        info.ID = Convert.ToInt32(dr["编码"]);
                        info.CardCode = dr["卡号"].ToString();
                        info.Shift = dr["班次"].ToString();
                        info.DriverWorkID = dr["司机工号"].ToString();
                        info.DriverName = dr["司机姓名"].ToString();
                        info.RefuelMoney = dr["加油金额"].ToString();
                        info.Remark = dr["备注"].ToString();
                        info.FillPerson = dr["填写人"].ToString();
                        info.FillTime = dr["填写时间"].ToString();
                        info.RefuelTime = dr["加油时间"].ToString();
                        info.LastGasPathCode = dr["上次加油路码"].ToString();
                        info.GasPathCode = dr["本次加油路码"].ToString();
                        info.DrivingKM = dr["行驶公里"].ToString();
                        info.Center = dr["分中心"].ToString();
                        info.Station = dr["分站"].ToString();
                        info.AmbulanceCode = dr["车辆编码"].ToString();
                        info.AmbulanceNumber = dr["车辆编号"].ToString();
                        info.ActualCode = dr["车牌号码"].ToString();
                        info.OilMass = dr["加油量"].ToString();
                        info.OilType = dr["加油油号"].ToString();
                        info.RefuelPerson = dr["加油人"].ToString();
                        info.ProofNumber = dr["凭证编号"].ToString();
                        info.CardAmount = dr["卡内金额"].ToString();
                        info.CardRemaining = dr["卡内余额"].ToString();
                        list.Add(info);
                    }
                }
                int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                var result = new { total = total, rows = list };
                return result;
            }
        }

        //根据车牌号码查出车辆编号和车辆编码，分中心，分站
        public C_OilCardLink GetActualCodeLinkList(string actualCode)
        {
            List<C_OilCardLink> list = new List<C_OilCardLink>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"  select 车辆编号=CarNumber,车辆编码=ca.AmbulanceCode,分站编码=StationCode,分中心编码=CenterCode,分站=ts.名称,分中心=tc.名称,
      加油时间=isnull((select max(t.RefuelTime) from(select  RefuelTime from C_OilCardRefuel ocr where ocr.ActualCode='" + actualCode + @"')t),''),
      本次加油路码=isnull((select GasPathCode from   C_OilCardRefuel where RefuelTime=(select max(t.RefuelTime) from(select  RefuelTime from C_OilCardRefuel ocr where ocr.ActualCode='" + actualCode + @"')t) and ActualCode='" + actualCode + @"'),0),
      竣工时间=isnull((select max(t.CompletionTime) from (select CompletionTime from C_RepairMaintain rm where rm.ActualCode='" + actualCode + @"')t) ,''),
      是否更改路码=isnull((select IsChangeRoadCode from  C_RepairMaintain  where CompletionTime=(select max(t.CompletionTime) from (select CompletionTime from C_RepairMaintain rm where rm.ActualCode='" + actualCode + @"')t) and ActualCode='" + actualCode + @"'),0),
	  进厂里程表数=isnull((select InMileage from  C_RepairMaintain  where  CompletionTime=(select max(t.CompletionTime) from (select CompletionTime from C_RepairMaintain rm where rm.ActualCode='" + actualCode + @"')t) and ActualCode='" + actualCode + @"'),0),
	  出厂里程表数=isnull((select OutMileage  from  C_RepairMaintain  where CompletionTime=(select max(t.CompletionTime) from (select CompletionTime from C_RepairMaintain rm where rm.ActualCode='" + actualCode + @"')t) and ActualCode='" + actualCode + @"'),0) 
            from C_Ambulance  ca ");
            
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on ts.编码=ca.StationCode ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on tc.编码=ca.CenterCode  ");
            sb.Append("  where ca.PlateNumber='" + actualCode + @"' and ca.isActive=1 and ca.isRejected=0 ");
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                C_OilCardLink info = new C_OilCardLink();
                if (dr.Read())
                {
                    info.AmbulanceNumber = dr["车辆编号"].ToString();
                    info.AmbulanceCode = dr["车辆编码"].ToString();
                    info.StationID = dr["分站编码"].ToString();
                    info.CenterID = Convert.ToInt32(dr["分中心编码"]);
                    info.Station = dr["分站"].ToString();
                    info.Center = dr["分中心"].ToString();
                    info.GasPathCode = dr["本次加油路码"].ToString();
                    info.RefuelTime = Convert.ToDateTime(dr["加油时间"]);
                    info.CompletionTime = Convert.ToDateTime(dr["竣工时间"]);
                    info.IsChangeRoadCode =Convert.ToBoolean(dr["是否更改路码"]);
                    info.InMileage = dr["进厂里程表数"].ToString();
                    info.OutMileage = dr["出厂里程表数"].ToString();
                    list.Add(info);
                }
                return info;
            }

        }
        //根据车辆编号查出车牌号码和车辆编码,分中心，分站
        public C_OilCardLink GetAmbulanceNumberLinkList(string ambulanceNumber)
        {
            List<C_OilCardLink> list = new List<C_OilCardLink>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"  select 车牌号码=PlateNumber,车辆编码=ca.AmbulanceCode,分站编码=StationCode,分中心编码=CenterCode,分站=ts.名称,分中心=tc.名称,
      加油时间=isnull((select max(t.RefuelTime) from(select  RefuelTime from C_OilCardRefuel ocr where ocr.AmbulanceNumber='" + ambulanceNumber + @"')t),''),
      本次加油路码=isnull((select GasPathCode from   C_OilCardRefuel where RefuelTime=(select max(t.RefuelTime) from(select  RefuelTime from C_OilCardRefuel ocr where ocr.AmbulanceNumber='" + ambulanceNumber + @"')t) and AmbulanceNumber='" + ambulanceNumber + @"'),0),
      竣工时间=isnull((select max(t.CompletionTime) from (select CompletionTime from C_RepairMaintain rm where rm.AmbulanceNumber='" + ambulanceNumber + @"')t) ,''),
      是否更改路码=isnull((select IsChangeRoadCode from  C_RepairMaintain  where CompletionTime=(select max(t.CompletionTime) from (select CompletionTime from C_RepairMaintain rm where rm.AmbulanceNumber='" + ambulanceNumber + @"')t) and AmbulanceNumber='" + ambulanceNumber + @"'),0),
	  进厂里程表数=isnull((select InMileage from  C_RepairMaintain  where  CompletionTime=(select max(t.CompletionTime) from (select CompletionTime from C_RepairMaintain rm where rm.AmbulanceNumber='" + ambulanceNumber + @"')t) and AmbulanceNumber='" + ambulanceNumber + @"'),0),
	  出厂里程表数=isnull((select OutMileage  from  C_RepairMaintain  where CompletionTime=(select max(t.CompletionTime) from (select CompletionTime from C_RepairMaintain rm where rm.AmbulanceNumber='" + ambulanceNumber + @"')t) and AmbulanceNumber='" + ambulanceNumber + @"'),0) 
            from C_Ambulance  ca ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on ts.编码=ca.StationCode ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on tc.编码=ca.CenterCode  ");
            sb.Append(" where CarNumber='" + ambulanceNumber + "' and isActive=1 and isRejected=0 ");
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                C_OilCardLink info = new C_OilCardLink();
                if (dr.Read())
                {
                    info.ActualCode = dr["车牌号码"].ToString();
                    info.AmbulanceCode = dr["车辆编码"].ToString();
                    info.StationID = dr["分站编码"].ToString();
                    info.CenterID = Convert.ToInt32(dr["分中心编码"]);
                    info.Station = dr["分站"].ToString();
                    info.Center = dr["分中心"].ToString();
                    info.GasPathCode = dr["本次加油路码"].ToString();
                    info.RefuelTime = Convert.ToDateTime(dr["加油时间"]);
                    info.CompletionTime = Convert.ToDateTime(dr["竣工时间"]);
                    info.IsChangeRoadCode = Convert.ToBoolean(dr["是否更改路码"]);
                    info.InMileage = dr["进厂里程表数"].ToString();
                    info.OutMileage = dr["出厂里程表数"].ToString();
                    list.Add(info);
                }
                return info;
            }

        }

        //获取车牌号码(有效且没报废的)
        public object GetActualCodeList(E_StatisticsPermisson em, string selfWorkCode, string selfCenterID, string selfStationID)
        {
            List<P_User> list = new List<P_User>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct 车牌号码=PlateNumber  from C_Ambulance ca ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            //sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TPerson tp on tp.分站编码=ca.StationCode ");
            //sb.Append(" left join  P_Department  pd  on pd.DispatchSationID = ca.StationCode ");
            //sb.Append(" left join  P_User  pu  on pu.DepID = pd.ID ");
            
            sb.Append(" where ca.isActive=1 and ca.isRejected=0 ");
            if (em == E_StatisticsPermisson.ALL)
            { sb.Append("and 1=1"); }
            else if (em == E_StatisticsPermisson.CENTER)
            { WhereClauseUtility.AddStringEqual("ca.CenterCode", selfCenterID, sb); }
            else if (em == E_StatisticsPermisson.STATION)
            { WhereClauseUtility.AddStringEqual("ca.CenterCode", selfCenterID, sb); }
            else if (em == E_StatisticsPermisson.SELF)
            { WhereClauseUtility.AddStringEqual("ca.CenterCode", selfCenterID, sb); }
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    P_User info = new P_User();
                    info.Name = dr["车牌号码"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取车牌号码(所有的)
        public object GetAllActualCodeList()
        {
            List<P_User> list = new List<P_User>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select 车牌号码=PlateNumber  from C_Ambulance  ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    P_User info = new P_User();
                    info.Name = dr["车牌号码"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取车辆编号(有效且没报废的)
        public object GetAmbulanceNumberList(E_StatisticsPermisson em, string selfWorkCode, string selfCenterID, string selfStationID)
        {
            List<P_User> list = new List<P_User>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct 车辆编号=CarNumber  from C_Ambulance ca ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            //sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TPerson tp on tp.分站编码=ca.StationCode ");
            //sb.Append(" left join  P_Department  pd  on pd.DispatchSationID = ca.StationCode ");
            //sb.Append(" left join  P_User  pu  on pu.DepID = pd.ID ");
            sb.Append(" where ca.isActive=1 and ca.isRejected=0 ");
            if (em == E_StatisticsPermisson.ALL)
            { sb.Append("and 1=1"); }
            else if (em == E_StatisticsPermisson.CENTER)
            { WhereClauseUtility.AddStringEqual("ca.CenterCode", selfCenterID, sb); }
            else if (em == E_StatisticsPermisson.STATION)
            { WhereClauseUtility.AddStringEqual("ca.CenterCode", selfCenterID, sb); }
            else if (em == E_StatisticsPermisson.SELF)
            { WhereClauseUtility.AddStringEqual("ca.CenterCode", selfCenterID, sb); }
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    P_User info = new P_User();
                    info.Name = dr["车辆编号"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取车辆编号(所有的)
        public object GetAllAmbulanceNumberList()
        {
            List<P_User> list = new List<P_User>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select 车辆编号=CarNumber  from C_Ambulance  ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    P_User info = new P_User();
                    info.Name = dr["车辆编号"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取加油油号
        public object GetOilTypeList()
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select ID,Name  from TDictionary  where TypeID='CMOilType' and  IsActive=1 ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["Name"].ToString();
                    info.Name=dr["Name"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取班次
        public object GetShiftList()
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select ID,Name  from TDictionary  where TypeID='CMShift' and  IsActive=1 ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["Name"].ToString();
                    info.Name = dr["Name"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取修理类别
        public object GetRepairTypeList()
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select ID,Name  from TDictionary  where TypeID='CMRepairType' and  IsActive=1 ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["Name"].ToString();
                    info.Name = dr["Name"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取加油人
        public object GetRefuelPersonList()
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select ID,Name  from P_User  where  IsActive=1 ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["Name"].ToString();
                    info.Name = dr["Name"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }          
        #endregion
    }
}
